Environment: 10gR2 on CentOS 4
Assumptions:
1]The database is in archive log mode.
3]A good cold backup is available
3]A clean shutdown was performed.
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/oradata/burp/system01.dbf'
SQL>
The datafile is either missing or corrupted.
Since it’s the system datafile, we cannot open the database without recovering it.
[Had it been for other datafiles we could offline that tablespace/datafile and open the database]
1] Shutdown the database
2] Restore the system01.dbf from the latest backup to the datafile location.
3] Mount the database
4] SQL>Recover datafile 1;
5] SQL>Alter database open
==ARENA==
I have shutdown the database and restored the datafile from the latest cold backup and mounted it
Lets query for the status of the DB.
SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
NAME OPEN_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ---------------
BURP MOUNTED 497094 495256
Query the view v$recover_file to see which file needs recovery(of course in our case its system01.dbf).
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- --------------- ---------- ---------
1 ONLINE ONLINE 494404 20-SEP-08
Since we have restored the system01.dbf from the latest backup, we will need the archivelogs to recover
the datafile. So query the v$datafile_header to see at what checkpoint our new system01.dbf datafile is.
SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;
SUBSTR(NAME,1,50) REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf YES NO 494404
/u01/oradata/burp/undotbs01.dbf NO NO 497094
/u01/oradata/burp/sysaux01.dbf NO NO 497094
/u01/oradata/burp/users01.dbf NO NO 497094
/u01/oradata/burp/example01.dbf NO NO 497094
Except system01.dbf all other datafiles are at checkpoint 497094, so we need to apply the archive logs and
bring system01.dbf from 494404 to 497094.
SQL> col tablespace_name format a15
SQL> col sequence format 9999
SQL> col file_num format 99
SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;
FILE_NUM FILE_NAME TYPE VALIDITY CHK TABLESPACE_NAME STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
1 /u01/oradata/burp/system01.dbf 3 0 494404 SYSTEM 8192 12
2 /u01/oradata/burp/undotbs01.dbf 3 0 497094 UNDOTBS1 0 18
3 /u01/oradata/burp/sysaux01.dbf 3 0 497094 SYSAUX 0 18
4 /u01/oradata/burp/users01.dbf 3 0 497094 USERS 0 18
5 /u01/oradata/burp/example01.dbf 3 0 497094 EXAMPLE 0 18
The system01.dbf need logs from sequence 12(CHK 494404) to sequence 18(CHK 497094) to make it consistent.
Now, check the archive logs.
SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME ARCHIVE_NAME
---------- ---------- --------- --------------------------------------------------
1 12 20-SEP-08 /u01/oradata/arch/1_12_665715452.dbf
1 13 01-OCT-08 /u01/oradata/arch/1_13_665715452.dbf
1 14 01-OCT-08 /u01/oradata/arch/1_14_665715452.dbf
1 15 01-OCT-08 /u01/oradata/arch/1_15_665715452.dbf
Logs until sequence 15 are archived and what about 16,17 and 18 ?
Let us query the v$log:
SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
1 1 17 1 YES INACTIVE 495261
3 1 16 1 YES INACTIVE 495197
2 1 18 1 NO CURRENT 495416
The 16,17 and 18 are still with the redo logs. (The view shows that 16 and 17 are archived, so it should be in the archive destination)
Now we are ready to recover the system01.dbf datafile.
SQL> recover datafile 1;
Excerpt from the alert.log file
============================================================ Completed: ALTER DATABASE MOUNT Wed Oct 1 01:23:46 2008 ALTER DATABASE RECOVER datafile 1 Wed Oct 1 01:23:46 2008 Media Recovery Start ORA-279 signalled during: ALTER DATABASE RECOVER datafile 1 ... Wed Oct 1 01:23:49 2008 ALTER DATABASE RECOVER CONTINUE DEFAULT Wed Oct 1 01:23:49 2008 Media Recovery Log /u01/oradata/arch/1_12_665715452.dbf ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ... Wed Oct 1 01:23:51 2008 ALTER DATABASE RECOVER CONTINUE DEFAULT Wed Oct 1 01:23:51 2008 : : Wed Oct 1 01:23:52 2008 Media Recovery Log /u01/oradata/arch/1_15_665715452.dbf Wed Oct 1 01:23:53 2008 Recovery of Online Redo Log: Thread 1 Group 3 Seq 16 Reading mem 0 Mem# 0 errs 0: /u01/oradata/burp/redo03.log Wed Oct 1 01:23:53 2008 Recovery of Online Redo Log: Thread 1 Group 1 Seq 17 Reading mem 0 Mem# 0 errs 0: /u01/oradata/burp/redo01.log Wed Oct 1 01:23:53 2008 Recovery of Online Redo Log: Thread 1 Group 2 Seq 18 Reading mem 0 Mem# 0 errs 0: /u01/oradata/burp/redo02.log Wed Oct 1 01:23:53 2008 Media Recovery Complete (burp) Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT ===============================================================
Oracle used the archive logs 12 to 15 and then the redo logs 16,17 and 18.
SQL> select * from v$recover_file;
no rows selected
There are no files to be recovered.
SQL> select substr(name,1,50), recover, fuzzy, checkpoint_change# from v$datafile_header;
SUBSTR(NAME,1,50) REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/oradata/burp/system01.dbf NO NO 497093
/u01/oradata/burp/undotbs01.dbf NO NO 497094
/u01/oradata/burp/sysaux01.dbf NO NO 497094
/u01/oradata/burp/users01.dbf NO NO 497094
/u01/oradata/burp/example01.dbf NO NO 497094
SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;
FILE_NUM FILE_NAME TYPE VALIDITY CHK TABLESPACE_NAME STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
1 /u01/oradata/burp/system01.dbf 3 0 497093 SYSTEM 8192 18
2 /u01/oradata/burp/undotbs01.dbf 3 0 497094 UNDOTBS1 0 18
3 /u01/oradata/burp/sysaux01.dbf 3 0 497094 SYSAUX 0 18
4 /u01/oradata/burp/users01.dbf 3 0 497094 USERS 0 18
5 /u01/oradata/burp/example01.dbf 3 0 497094 EXAMPLE 0 18
All the datafile seems to be consistent.
Now we can ‘open’ the database.
SQL> alter database open;
SQL>select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
from X$KCVFH;
FILE_NUM FILE_NAME TYPE VALIDITY CHK TABLESPACE_NAME STATUS SEQUENCE
-------- ---------------------------------------- ---------- ---------- ---------------- --------------- ---------- --------
1 /u01/oradata/burp/system01.dbf 3 0 497095 SYSTEM 8196 18
2 /u01/oradata/burp/undotbs01.dbf 3 0 497095 UNDOTBS1 4 18
3 /u01/oradata/burp/sysaux01.dbf 3 0 497095 SYSAUX 4 18
4 /u01/oradata/burp/users01.dbf 3 0 497095 USERS 4 18
5 /u01/oradata/burp/example01.dbf 3 0 497095 EXAMPLE 4 18
Getting on terms with Oracle Technologies 
Pingback: Index « My confrontations with oracle
This was an excellent exercise . Well done and keep up such good post. Thank you.
Hi Guru,
I Got this error…ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDOV\SYSTEM01.DBF’
But I am unable to recover my system.dbf file.
Actual My File is in : ‘E:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDOV\SYSTEM01.DBF’
I want to rename ..How i can do it… as i can only mount the database
Pingback: Memindahkan Database Oracle dari server A ke server B dengan directory yang berbeda « Oracle, Web, Script, SQLserver, Tips & Trick
Very good post. Solved my problem.